package cn.kgc.jinxin.clothesstore.dao.impl;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import cn.kgc.jinxin.clothesstore.dao.BaseDao;
import cn.kgc.jinxin.clothesstore.dao.GoodsDao;
import cn.kgc.jinxin.clothesstore.entity.Goods;
import cn.kgc.jinxin.clothesstore.entity.PageInfo;

public class GoodsDaoImpl implements GoodsDao {

	@Override
	public void updateStock(String goodsId, Integer orderNum,Connection connection) throws Exception {
		PreparedStatement psm = null;
		try {
			String sql = "update goods set goodsStock = goodsStock-? where goodsId=?";
			psm = connection.prepareStatement(sql);
			psm.setInt(1, orderNum);
			psm.setString(2, goodsId);
			psm.executeUpdate();
		} finally {
			BaseDao.close(null, psm, null);
		}
		

	}

	@Override
	public List<Goods> selectAll() throws Exception {
		List<Goods> goodsList = new ArrayList<Goods>();
		Connection connection = null;
		PreparedStatement psm = null;
		ResultSet resultSet = null;
		try {
			connection = BaseDao.getConnection();
			String sql = "select goodsid,goodsname,goodsprice,goodspic from goods";
			psm = connection.prepareStatement(sql);
			resultSet = psm.executeQuery();
			Goods goods;
			while (resultSet.next()) {
				goods = new Goods();
				goods.setGoodsId(resultSet.getString(1));
				goods.setGoodsName(resultSet.getString(2));
				goods.setGoodsPrice(resultSet.getDouble(3));
				goods.setGoodspic(resultSet.getString(4));
				goodsList.add(goods);
			} 
		} finally {
			BaseDao.close(connection, psm, resultSet);
		}
		return goodsList;
	}

	@Override
	public Integer selectTotalRecordByPage(PageInfo goodsPageInfo,Connection connection) throws Exception {
		Integer totalRecord = null;
		PreparedStatement psm = null;
		ResultSet resultSet = null;
		try {
			connection = BaseDao.getConnection();
			StringBuilder sql = new StringBuilder( "select count(goodsid) from goods where 1=1 ");
			List list = new ArrayList();
			if(goodsPageInfo.getCondition()!=null) {
				if(goodsPageInfo.getCondition().getGoodsName()!=null) {
					sql.append(" and goodsname=?");
					list.add(goodsPageInfo.getCondition().getGoodsName());
				}
				if(goodsPageInfo.getCondition().getGoodsPrice()!=null) {
					sql.append(" and goodsprice>?");
					list.add(goodsPageInfo.getCondition().getGoodsPrice());
				}
			}		
			psm = connection.prepareStatement(sql.toString());
			for(int index=0;index<list.size();index++) {
				psm.setObject(index+1, list.get(index));
			}
			resultSet = psm.executeQuery();
			if (resultSet.next()) {
				totalRecord = resultSet.getInt(1);
			} 
		} finally {
			BaseDao.close(null, psm, resultSet);
		}
		return totalRecord;
	}

	@Override
	public List<Goods> selectByPage(Integer currentPage, Integer pageSize,Connection connection) throws Exception {
		List<Goods> goodsList = new ArrayList<Goods>();
		PreparedStatement psm = null;
		ResultSet resultSet = null;
		try {
			connection = BaseDao.getConnection();
			String sql = "select goodsid,goodsname,goodsprice,goodspic from goods order by goodsid desc limit ?,?";
			psm = connection.prepareStatement(sql);
			psm.setInt(1, (currentPage - 1) * pageSize);
			psm.setInt(2, pageSize);
			resultSet = psm.executeQuery();
			Goods goods;
			while (resultSet.next()) {
				goods = new Goods();
				goods.setGoodsId(resultSet.getString("goodsid"));
				goods.setGoodsName(resultSet.getString("goodsname"));
				goods.setGoodsPrice(resultSet.getDouble("goodsprice"));
				goods.setGoodspic(resultSet.getString("goodspic"));
				goodsList.add(goods);
			} 
		} finally {
			BaseDao.close(null, psm, resultSet);
		}
		return goodsList;
	}

	@Override
	public List<Goods> selectByPage(PageInfo<Goods> goodsPageInfo, Connection connection) throws Exception {
		List<Goods> goodsList = new ArrayList<Goods>();
		PreparedStatement psm = null;
		ResultSet resultSet = null;
		try {
			connection = BaseDao.getConnection();
			List list = new ArrayList();
			StringBuilder sql = new StringBuilder("select goodsid,goodsname,goodsprice,goodspic "
					+ "from goods where 1=1 ");
			if(goodsPageInfo.getCondition()!=null) {
				if(goodsPageInfo.getCondition().getGoodsName()!=null) {
					sql.append(" and goodsname=?");
					list.add(goodsPageInfo.getCondition().getGoodsName());
				}
				if(goodsPageInfo.getCondition().getGoodsPrice()!=null) {
					sql.append(" and goodsprice>?");
					list.add(goodsPageInfo.getCondition().getGoodsPrice());
				}
			}
			sql.append(" order by goodsid desc limit ?,?");
			list.add(goodsPageInfo.getFrom());
			list.add(goodsPageInfo.getPageSize());
			psm = connection.prepareStatement(sql.toString());
			for(int index=0;index<list.size();index++) {
				psm.setObject(index+1, list.get(index));
			}
			resultSet = psm.executeQuery();
			Goods goods;
			while (resultSet.next()) {
				goods = new Goods();
				goods.setGoodsId(resultSet.getString("goodsid"));
				goods.setGoodsName(resultSet.getString("goodsname"));
				goods.setGoodsPrice(resultSet.getDouble("goodsprice"));
				goods.setGoodspic(resultSet.getString("goodspic"));
				goodsList.add(goods);
			} 
		} finally {
			BaseDao.close(null, psm, resultSet);
		}
		return goodsList;
	}

	@Override
	public Integer selectTotalRecordByPage(Connection connection) throws Exception {
		Integer totalRecord = null;
		PreparedStatement psm = null;
		ResultSet resultSet = null;
		try {
			connection = BaseDao.getConnection();
			String sql = new String("select count(goodsid) from goods");			
			psm = connection.prepareStatement(sql);
			resultSet = psm.executeQuery();
			if (resultSet.next()) {
				totalRecord = resultSet.getInt(1);
			} 
		} finally {
			BaseDao.close(null, psm, resultSet);
		}
		return totalRecord;
	}

}
